/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.exceptions.CursoInexistenteException;
import br.com.ufpb.halloc.exceptions.CursoInvalidoException;
import br.com.ufpb.halloc.negocio.Curso;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Kawe Ramon
 */
public class CursoDAOPostgresSQL implements CursoDAO{

    private static CursoDAOPostgresSQL daoCursoPostgresSQl;
    private Pool pool;
    
    public static CursoDAOPostgresSQL getInstance(){
        
        if(daoCursoPostgresSQl==null){
            return new CursoDAOPostgresSQL();
        }
        return daoCursoPostgresSQl;
    }
    
    public CursoDAOPostgresSQL(){
        
        this.pool = new Pool();
    }
    
    
    @Override
    public void inserir(Curso curso) throws CursoInexistenteException{
        
        Connection connection = this.pool.getConnection();
        PreparedStatement prepared;
        String sqlInsert = "INSERT INTO curso (codcurso, nomecurso, nrdepartamento) VALUES(?,?,?);";
        if(curso != null){
            try {
            prepared = connection.prepareStatement(sqlInsert);
            prepared.setString(1, curso.getID());
            prepared.setString(2, curso.getNome());
            prepared.setString(3, curso.getNumeroDepartamento());            
            prepared.execute();
            prepared.close();
        } catch (SQLException e) {
        }finally{
                pool.liberarConnection(connection);
            }
        }else{
            throw new CursoInexistenteException("Curso Vazio");
        }
        
    }

    @Override
    public void remover(Curso curso) {
        
        String sqlDelete = "DELETE FROM curso WHERE codcurso = '"+curso.getID()+"';";
        Connection connection = this.pool.getConnection();
        PreparedStatement prepared;
        try {
            prepared = connection.prepareStatement(sqlDelete);
            prepared.executeUpdate();
            prepared.close();
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            pool.liberarConnection(connection);
        }
    }

    public void removerPorId(String id){
        
        Connection connection = this.pool.getConnection();
        PreparedStatement prepared = null;
        try {
            prepared = connection.prepareStatement("DELETE FROM curso WHERE codcurso = '"+id+"';");
            prepared.executeUpdate();
            prepared.close();
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            this.pool.liberarConnection(connection);
        }
        
    }
    
    
    @Override
    public List<Curso> pesquisarNome(String nome) {
       
        List<Curso> cursos = new ArrayList<Curso>();
        Connection connection = this.pool.getConnection();
        PreparedStatement pr;
        try {
            pr = connection.prepareStatement("SELECT * FROM curso WHERE LOWER(nomecurso) LIKE '"+nome.toLowerCase()+"%';");
            ResultSet result = pr.executeQuery();
            while(result.next()){
                Curso c = new Curso(result.getString("nomecurso"), 
                                    result.getString("nrdepartamento"), 
                                    result.getString("codcurso"));
                cursos.add(c);
            }
            
            pr.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            this.pool.liberarConnection(connection);
        }
        
        return cursos;
    }

    @Override
    public Curso pesquisarId(String id) throws CursoInexistenteException, CursoInvalidoException{
        
        Connection connection = this.pool.getConnection();
        PreparedStatement prepared = null;;
        Curso curso = null;
        if(id != null){
            try {
            prepared = connection.prepareStatement("SELECT * FROM curso WHERE codcurso = '"+ id +"';");
            ResultSet result = prepared.executeQuery();
            
            while(result.next()){
                curso = new Curso(result.getString("nomecurso"), result.getString("nrdepartamento"), result.getString("codcurso"));                
            }
            prepared.close();
            } catch (SQLException ex) {
                Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
            }finally{
                pool.liberarConnection(connection);
            }
        }else{
            throw new CursoInvalidoException("Id vazio");
        }
        
        
        return curso;
    }

    @Override
    public void atualizar(Curso curso) throws CursoInvalidoException{
        Connection connection = null;
        PreparedStatement prepared;
        connection = pool.getConnection();
        
        if(curso!=null){
            try {
                prepared = connection.prepareCall("UPDATE curso SET nomecurso = '"+curso.getNome()+ "' , "
                                                  + "nrdepartamento = '"+curso.getNumeroDepartamento()+
                                                  "' , codcurso = '"+curso.getID()+"';");
                prepared.executeUpdate();
                prepared.close();
                
            } catch (SQLException ex) {
                Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
            }finally{
                this.pool.liberarConnection(connection);                        
            }
        }else{
            throw new CursoInvalidoException("Curso Vazio");
        }
        
        
        
        
        
    }

    @Override
    public List<Curso> getCursos() {
        
        List<Curso> cursos = new ArrayList<Curso>();
        
        Connection con = this.pool.getConnection();
        PreparedStatement pr;
        try {
            pr = con.prepareStatement("SELECT * FROM curso ORDER BY nomecurso");
            ResultSet result = pr.executeQuery();
            while(result.next()){
                Curso curso = new Curso(result.getString("nomecurso"), 
                                        result.getString("nrdepartamento"), result.getString("codcurso"));
                
                cursos.add(curso);
            }
            
            pr.close();
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAOPostgresSQL.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            this.pool.liberarConnection(con);
        }
        
        return cursos;
    }
    
    
    
    
    
}
